{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2e105d4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# DSL风格 --DataFrame API\n",
    "# spark提供DSL方法和sql的关键词一样，使用方式和sql基本类似，\n",
    "# 在进行数据处理时，要按照sql的执行顺序去思考如何处理数据\n",
    "# DSL方法执行完成后会得到一个处理后的新的df\n",
    "# from  join    知道数据在哪   df本身就是要处理的数据  df.join(df2)\n",
    "# where         过滤需要处理的数据   df.join(df2).where()\n",
    "# group by     聚合  数据的计算     df.join(df2).where().groupby().sum()\n",
    "# select        展示数据的字段  df.join(df2).where().groupby().sum().where().select()\n",
    "# order by      展示数据的排序   df.join(df2).where().groupby().sum().where().select().orderBy()\n",
    "# limit         展示数据的数量  df.join(df2).where().groupby().sum().where().select().orderBy().limit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ae97a6b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()\n",
    "from pyspark.sql import SparkSession, functions as F\n",
    "from pyspark.sql.types import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "03095ef3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 生成SparkSession 对象\n",
    "spark = SparkSession.builder.getOrCreate()\n",
    "sc = spark.sparkContext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3e420a6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从文件读取学生信息，读取文件生成rdd数据\n",
    "rdd = sc.textFile(\"file:///C:/Users/rckel/Desktop/demo/students.txt\")\n",
    "\n",
    "table_rdd = rdd.map(\n",
    "    lambda x: [int(x.split(',')[0]), x.split(',')[1], x.split(',')[2], int(x.split(',')[3]), x.split(',')[4]])\n",
    "\n",
    "# 定义schedule信息，指定字段名和字段类型\n",
    "schema_type = StructType(). \\\n",
    "    add('id', IntegerType()). \\\n",
    "    add('name', StringType()). \\\n",
    "    add('gender', StringType()). \\\n",
    "    add('age', IntegerType()). \\\n",
    "    add('cls', StringType())\n",
    "\n",
    "df = table_rdd.toDF(schema_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ddfd4e8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----+------+---+---+\n",
      "| id|name|gender|age|cls|\n",
      "+---+----+------+---+---+\n",
      "|  1|   a|     m| 10|  x|\n",
      "|  2|   b|     f| 11|  y|\n",
      "+---+----+------+---+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4d104229",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|name|\n",
      "+----+\n",
      "|   a|\n",
      "|   b|\n",
      "+----+\n",
      "\n",
      "+----+---+\n",
      "|name|age|\n",
      "+----+---+\n",
      "|   a| 10|\n",
      "|   b| 11|\n",
      "+----+---+\n",
      "\n",
      "+----+------+\n",
      "|name|gender|\n",
      "+----+------+\n",
      "|   a|     m|\n",
      "|   b|     f|\n",
      "+----+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select('name').show()\n",
    "df.select(['name', 'age']).show()\n",
    "df.select(df['name'], df['gender']).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "91ab8198",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+---+--------+\n",
      "|gender|cls|sum(age)|\n",
      "+------+---+--------+\n",
      "|     m|  x|      10|\n",
      "|     f|  y|      11|\n",
      "+------+---+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(['gender','cls']).sum('age').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "918800ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 生成rdd\n",
    "rdd1 = sc.parallelize([\n",
    "    [1, 'zhangsan', 20],\n",
    "    [2, 'lisi', 20],\n",
    "    [3, 'wangwu', 22]\n",
    "])\n",
    "\n",
    "rdd2 = sc.parallelize([\n",
    "    [3, 'zhaoliu', 20],\n",
    "    [4, 'xiaoming', 21],\n",
    "    [5, 'itcast', 22]\n",
    "])\n",
    "\n",
    "# 定义schema信息\n",
    "schema_type = StructType(). \\\n",
    "    add('id', IntegerType()). \\\n",
    "    add('name', StringType()). \\\n",
    "    add('age', IntegerType(), False)\n",
    "\n",
    "# toDF 将二维rdd数据转为dataframe数据\n",
    "df1 = rdd1.toDF(schema_type)\n",
    "df2 = rdd2.toDF(schema_type)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "71ea81ad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+--------+---+\n",
      "| id|    name|age|\n",
      "+---+--------+---+\n",
      "|  1|zhangsan| 20|\n",
      "|  2|    lisi| 20|\n",
      "|  3|  wangwu| 22|\n",
      "+---+--------+---+\n",
      "\n",
      "+---+--------+---+\n",
      "| id|    name|age|\n",
      "+---+--------+---+\n",
      "|  3| zhaoliu| 20|\n",
      "|  4|xiaoming| 21|\n",
      "|  5|  itcast| 22|\n",
      "+---+--------+---+\n",
      "\n",
      "------------join_rdd----------------\n",
      "+---+------+---+-------+---+\n",
      "| id|  name|age|   name|age|\n",
      "+---+------+---+-------+---+\n",
      "|  3|wangwu| 22|zhaoliu| 20|\n",
      "+---+------+---+-------+---+\n",
      "\n",
      "------------left_join_rdd----------------\n",
      "+---+--------+---+-------+----+\n",
      "| id|    name|age|   name| age|\n",
      "+---+--------+---+-------+----+\n",
      "|  1|zhangsan| 20|   null|null|\n",
      "|  2|    lisi| 20|   null|null|\n",
      "|  3|  wangwu| 22|zhaoliu|  20|\n",
      "+---+--------+---+-------+----+\n",
      "\n",
      "------------lright_join_rdd----------------\n",
      "+---+------+----+--------+---+\n",
      "| id|  name| age|    name|age|\n",
      "+---+------+----+--------+---+\n",
      "|  3|wangwu|  22| zhaoliu| 20|\n",
      "|  4|  null|null|xiaoming| 21|\n",
      "|  5|  null|null|  itcast| 22|\n",
      "+---+------+----+--------+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# join的关联\n",
    "# 内关联 返回新的rdd\n",
    "# 第一个参数 ：关联的df\n",
    "# 第二个参数 ：关联的字段\n",
    "# 第二个参数 ：关联方式  默认不写是内关联\n",
    "join_rdd = df1.join(df2,'id')\n",
    "# 左\n",
    "left_join_rdd = df1.join(df2,'id','left')\n",
    "# 右\n",
    "right_join_rdd = df1.join(df2,'id','right')\n",
    "# 查看df数据\n",
    "df1.show()\n",
    "df2.show()\n",
    "print('------------join_rdd----------------')\n",
    "join_rdd.show()\n",
    "print('------------left_join_rdd----------------')\n",
    "left_join_rdd.show()\n",
    "print('------------lright_join_rdd----------------')\n",
    "right_join_rdd.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "08232594",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+--------+---+------+----------+----------+\n",
      "| id|    name|age|gender|      date| timestamp|\n",
      "+---+--------+---+------+----------+----------+\n",
      "|  1|zhangsan| 20|    男|1989-01-01|1667114341|\n",
      "|  2|    lisi| 20|    男|1991-01-01|1667114341|\n",
      "|  3|  wangwu| 22|    男|2020-01-01|1667114341|\n",
      "+---+--------+---+------+----------+----------+\n",
      "\n",
      "root\n",
      " |-- id: long (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- age: long (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- date: string (nullable = true)\n",
      " |-- timestamp: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 生成rdd\n",
    "rdd1 = sc.parallelize([\n",
    "    [1, 'zhangsan', 20, '男', '1989-01-01', '1667114341'],\n",
    "    [2, 'lisi', 20, '男', '1991-01-01', '1667114341'],\n",
    "    [3, 'wangwu', 22, '男', '2020-01-01', '1667114341']\n",
    "])\n",
    "\n",
    "# 定义schema信息\n",
    "schema_type = StructType(). \\\n",
    "    add('id', IntegerType()). \\\n",
    "    add('name', StringType()). \\\n",
    "    add('age', IntegerType(), False). \\\n",
    "    add('gender', StringType()). \\\n",
    "    add('date', StringType()). \\\n",
    "    add('unix_t', StringType())\n",
    "\n",
    "# toDF 将二维rdd数据转为dataframe数据\n",
    "df = rdd1.toDF(schema_type)\n",
    "\n",
    "df2 = rdd1.toDF([\"id\",\"name\",\"age\",\"gender\",\"date\",\"timestamp\"])\n",
    "df2.show()\n",
    "df2.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "de5feaf9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+--------+---+------+----------+----------+\n",
      "| id|    name|age|gender|      date|    unix_t|\n",
      "+---+--------+---+------+----------+----------+\n",
      "|  1|zhangsan| 20|    男|1989-01-01|1667114341|\n",
      "|  2|    lisi| 20|    男|1991-01-01|1667114341|\n",
      "|  3|  wangwu| 22|    男|2020-01-01|1667114341|\n",
      "+---+--------+---+------+----------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "4a59fad8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+\n",
      "|concat(name, gender)|\n",
      "+--------------------+\n",
      "|          zhangsan男|\n",
      "|              lisi男|\n",
      "|            wangwu男|\n",
      "+--------------------+\n",
      "\n",
      "+--------------------------+\n",
      "|concat_ws(,, name, gender)|\n",
      "+--------------------------+\n",
      "|               zhangsan,男|\n",
      "|                   lisi,男|\n",
      "|                 wangwu,男|\n",
      "+--------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(F.concat('name', 'gender')).show()\n",
    "df.select(F.concat_ws(',', 'name', 'gender')).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8eb666d5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+\n",
      "|current_date()|\n",
      "+--------------+\n",
      "|    2024-11-08|\n",
      "|    2024-11-08|\n",
      "|    2024-11-08|\n",
      "+--------------+\n",
      "\n",
      "+--------------------+\n",
      "| current_timestamp()|\n",
      "+--------------------+\n",
      "|2024-11-08 05:40:...|\n",
      "|2024-11-08 05:40:...|\n",
      "|2024-11-08 05:40:...|\n",
      "+--------------------+\n",
      "\n",
      "+--------------------------------------------------------+\n",
      "|unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)|\n",
      "+--------------------------------------------------------+\n",
      "|                                              1731015648|\n",
      "|                                              1731015648|\n",
      "|                                              1731015648|\n",
      "+--------------------------------------------------------+\n",
      "\n",
      "+------------------------------------------+\n",
      "|from_unixtime(unix_t, yyyy/MM/dd HH:mm:ss)|\n",
      "+------------------------------------------+\n",
      "|                       2022/10/30 15:19:01|\n",
      "|                       2022/10/30 15:19:01|\n",
      "|                       2022/10/30 15:19:01|\n",
      "+------------------------------------------+\n",
      "\n",
      "+------------------+\n",
      "|date_add(date, -1)|\n",
      "+------------------+\n",
      "|        1988-12-31|\n",
      "|        1990-12-31|\n",
      "|        2019-12-31|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 时间操作\n",
    "# 获取当前日期\n",
    "df.select(F.current_date()).show()\n",
    "# 获取当前日期时间\n",
    "df.select(F.current_timestamp()).show()\n",
    "# 获取unix时间\n",
    "df.select(F.unix_timestamp()).show()\n",
    "\n",
    "# 将unix时间转为标准时间\n",
    "df.select(F.from_unixtime('unix_t', format=\"yyyy/MM/dd HH:mm:ss\")).show()\n",
    "\n",
    "# 时间加减\n",
    "df.select(F.date_add('date', -1)).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "96efb1c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "| id|pid|shortname|    name|                mergename|level|     pinyin|code|   zip|first|       lng|      lat|\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "|  1|  0|     北京|    北京|                中国,北京|    1|    beijing|    |      |    B|116.405285|39.904989|\n",
      "|  2|  1|     北京|  北京市|         中国,北京,北京市|    2|    beijing| 010|100000|    B|116.405285|39.904989|\n",
      "|  3|  2|     东城|  东城区|  中国,北京,北京市,东城区|    3|  dongcheng| 010|100010|    D| 116.41005| 39.93157|\n",
      "|  4|  2|     西城|  西城区|  中国,北京,北京市,西城区|    3|    xicheng| 010|100032|    X| 116.36003|  39.9305|\n",
      "|  5|  2|     朝阳|  朝阳区|  中国,北京,北京市,朝阳区|    3|   chaoyang| 010|100020|    C| 116.48548|  39.9484|\n",
      "|  6|  2|     丰台|  丰台区|  中国,北京,北京市,丰台区|    3|    fengtai| 010|100071|    F| 116.28625|  39.8585|\n",
      "|  7|  2|   石景山|石景山区|中国,北京,北京市,石景山区|    3|shijingshan| 010|100043|    S|  116.2229| 39.90564|\n",
      "|  8|  2|     海淀|  海淀区|  中国,北京,北京市,海淀区|    3|    haidian| 010|100089|    H| 116.29812| 39.95931|\n",
      "|  9|  2|   门头沟|门头沟区|中国,北京,北京市,门头沟区|    3|  mentougou| 010|102300|    M| 116.10137| 39.94043|\n",
      "| 10|  2|     房山|  房山区|  中国,北京,北京市,房山区|    3|   fangshan| 010|102488|    F| 116.14257| 39.74786|\n",
      "| 11|  2|     通州|  通州区|  中国,北京,北京市,通州区|    3|   tongzhou| 010|101149|    T| 116.65716| 39.90966|\n",
      "| 12|  2|     顺义|  顺义区|  中国,北京,北京市,顺义区|    3|     shunyi| 010|101300|    S| 116.65417|  40.1302|\n",
      "| 13|  2|     昌平|  昌平区|  中国,北京,北京市,昌平区|    3|  changping| 010|102200|    C|  116.2312| 40.22072|\n",
      "| 14|  2|     大兴|  大兴区|  中国,北京,北京市,大兴区|    3|     daxing| 010|102600|    D| 116.34149| 39.72668|\n",
      "| 15|  2|     怀柔|  怀柔区|  中国,北京,北京市,怀柔区|    3|    huairou| 010|101400|    H| 116.63168| 40.31602|\n",
      "| 16|  2|     平谷|  平谷区|  中国,北京,北京市,平谷区|    3|     pinggu| 010|101200|    P| 117.12133| 40.14056|\n",
      "| 17|  2|     密云|  密云县|  中国,北京,北京市,密云县|    3|      miyun| 010|101500|    M| 116.84295| 40.37618|\n",
      "| 18|  2|     延庆|  延庆县|  中国,北京,北京市,延庆县|    3|    yanqing| 010|102100|    Y| 115.97494| 40.45672|\n",
      "| 19|  0|     天津|    天津|                中国,天津|    1|    tianjin|    |      |    T|117.190182|39.125596|\n",
      "| 20| 19|     天津|  天津市|         中国,天津,天津市|    2|    tianjin| 022|300000|    T|117.190182|39.125596|\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sql_df = spark.read.jdbc(\n",
    "    url='jdbc:mysql://127.0.0.1:3307/ait?characterEncoding=UTF-8',\n",
    "    table='area',\n",
    "    properties={'user':'root','password':'123123','dirver':'com.mysql.jdbc.Driver'})\n",
    "\n",
    "sql_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6eeb1698",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "| id|pid|shortname|    name|                mergename|level|     pinyin|code|   zip|first|       lng|      lat|\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "|  1|  0|     北京|    北京|                中国,北京|    1|    beijing|    |      |    B|116.405285|39.904989|\n",
      "|  2|  1|     北京|  北京市|         中国,北京,北京市|    2|    beijing| 010|100000|    B|116.405285|39.904989|\n",
      "|  3|  2|     东城|  东城区|  中国,北京,北京市,东城区|    3|  dongcheng| 010|100010|    D| 116.41005| 39.93157|\n",
      "|  4|  2|     西城|  西城区|  中国,北京,北京市,西城区|    3|    xicheng| 010|100032|    X| 116.36003|  39.9305|\n",
      "|  5|  2|     朝阳|  朝阳区|  中国,北京,北京市,朝阳区|    3|   chaoyang| 010|100020|    C| 116.48548|  39.9484|\n",
      "|  6|  2|     丰台|  丰台区|  中国,北京,北京市,丰台区|    3|    fengtai| 010|100071|    F| 116.28625|  39.8585|\n",
      "|  7|  2|   石景山|石景山区|中国,北京,北京市,石景山区|    3|shijingshan| 010|100043|    S|  116.2229| 39.90564|\n",
      "|  8|  2|     海淀|  海淀区|  中国,北京,北京市,海淀区|    3|    haidian| 010|100089|    H| 116.29812| 39.95931|\n",
      "|  9|  2|   门头沟|门头沟区|中国,北京,北京市,门头沟区|    3|  mentougou| 010|102300|    M| 116.10137| 39.94043|\n",
      "| 10|  2|     房山|  房山区|  中国,北京,北京市,房山区|    3|   fangshan| 010|102488|    F| 116.14257| 39.74786|\n",
      "| 11|  2|     通州|  通州区|  中国,北京,北京市,通州区|    3|   tongzhou| 010|101149|    T| 116.65716| 39.90966|\n",
      "| 12|  2|     顺义|  顺义区|  中国,北京,北京市,顺义区|    3|     shunyi| 010|101300|    S| 116.65417|  40.1302|\n",
      "| 13|  2|     昌平|  昌平区|  中国,北京,北京市,昌平区|    3|  changping| 010|102200|    C|  116.2312| 40.22072|\n",
      "| 14|  2|     大兴|  大兴区|  中国,北京,北京市,大兴区|    3|     daxing| 010|102600|    D| 116.34149| 39.72668|\n",
      "| 15|  2|     怀柔|  怀柔区|  中国,北京,北京市,怀柔区|    3|    huairou| 010|101400|    H| 116.63168| 40.31602|\n",
      "| 16|  2|     平谷|  平谷区|  中国,北京,北京市,平谷区|    3|     pinggu| 010|101200|    P| 117.12133| 40.14056|\n",
      "| 17|  2|     密云|  密云县|  中国,北京,北京市,密云县|    3|      miyun| 010|101500|    M| 116.84295| 40.37618|\n",
      "| 18|  2|     延庆|  延庆县|  中国,北京,北京市,延庆县|    3|    yanqing| 010|102100|    Y| 115.97494| 40.45672|\n",
      "| 19|  0|     天津|    天津|                中国,天津|    1|    tianjin|    |      |    T|117.190182|39.125596|\n",
      "| 20| 19|     天津|  天津市|         中国,天津,天津市|    2|    tianjin| 022|300000|    T|117.190182|39.125596|\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "| id|pid|shortname|    name|                mergename|level|     pinyin|code|   zip|first|       lng|      lat|\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "|  1|  0|     北京|    北京|                中国,北京|    1|    beijing|    |      |    B|116.405285|39.904989|\n",
      "|  2|  1|     北京|  北京市|         中国,北京,北京市|    2|    beijing| 010|100000|    B|116.405285|39.904989|\n",
      "|  3|  2|     东城|  东城区|  中国,北京,北京市,东城区|    3|  dongcheng| 010|100010|    D| 116.41005| 39.93157|\n",
      "|  4|  2|     西城|  西城区|  中国,北京,北京市,西城区|    3|    xicheng| 010|100032|    X| 116.36003|  39.9305|\n",
      "|  5|  2|     朝阳|  朝阳区|  中国,北京,北京市,朝阳区|    3|   chaoyang| 010|100020|    C| 116.48548|  39.9484|\n",
      "|  6|  2|     丰台|  丰台区|  中国,北京,北京市,丰台区|    3|    fengtai| 010|100071|    F| 116.28625|  39.8585|\n",
      "|  7|  2|   石景山|石景山区|中国,北京,北京市,石景山区|    3|shijingshan| 010|100043|    S|  116.2229| 39.90564|\n",
      "|  8|  2|     海淀|  海淀区|  中国,北京,北京市,海淀区|    3|    haidian| 010|100089|    H| 116.29812| 39.95931|\n",
      "|  9|  2|   门头沟|门头沟区|中国,北京,北京市,门头沟区|    3|  mentougou| 010|102300|    M| 116.10137| 39.94043|\n",
      "| 10|  2|     房山|  房山区|  中国,北京,北京市,房山区|    3|   fangshan| 010|102488|    F| 116.14257| 39.74786|\n",
      "+---+---+---------+--------+-------------------------+-----+-----------+----+------+-----+----------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df2 = spark.read \\\n",
    "    .format(\"jdbc\") \\\n",
    "    .option(\"url\", \"jdbc:mysql://127.0.0.1:3307/ait?characterEncoding=UTF-8\") \\\n",
    "    .option(\"dbtable\", \"area\") \\\n",
    "    .option(\"user\", \"root\") \\\n",
    "    .option(\"password\", \"123123\") \\\n",
    "    .load() \n",
    "\n",
    "df2.show()\n",
    "\n",
    "df3 = spark.read \\\n",
    "    .format(\"jdbc\") \\\n",
    "    .option(\"url\", \"jdbc:mysql://127.0.0.1:3307/ait?characterEncoding=UTF-8\") \\\n",
    "    .option(\"query\",\"select * from area limit 10\") \\\n",
    "    .option(\"user\", \"root\") \\\n",
    "    .option(\"password\", \"123123\") \\\n",
    "    .load() \n",
    "df3.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d62a5e10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+\n",
      "|name|age|\n",
      "+----+---+\n",
      "| tom| 24|\n",
      "| jim| 20|\n",
      "|jack| 22|\n",
      "+----+---+\n",
      "\n",
      "+----+---+\n",
      "|  _1| _2|\n",
      "+----+---+\n",
      "| tom| 24|\n",
      "| jim| 20|\n",
      "|jack| 22|\n",
      "+----+---+\n",
      "\n",
      "+----+---+\n",
      "|name|age|\n",
      "+----+---+\n",
      "| tom| 24|\n",
      "| jim| 20|\n",
      "|jack| 22|\n",
      "+----+---+\n",
      "\n",
      "+----+---+\n",
      "|name|age|\n",
      "+----+---+\n",
      "| tom| 24|\n",
      "| jim| 20|\n",
      "|jack| 22|\n",
      "+----+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "a = [('tom',24),('jim',20),('jack',22)]\n",
    "df1 = spark.createDataFrame(a)\n",
    "df2 = spark.createDataFrame(a, \"name:string, age:Int\")\n",
    "df3 = spark.createDataFrame(a, \"name string, age integer\")\n",
    "df = spark.createDataFrame(a, ['name','age'])\n",
    "df.show()\n",
    "df1.show()\n",
    "df2.show()\n",
    "df3.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3aac58df",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|total|\n",
      "+-----+\n",
      "| 22.0|\n",
      "+-----+\n",
      "\n",
      "+----+---+------------+\n",
      "|name|age|(age + 1000)|\n",
      "+----+---+------------+\n",
      "| tom| 24|        1024|\n",
      "| jim| 20|        1020|\n",
      "|jack| 22|        1022|\n",
      "+----+---+------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.selectExpr(\"avg(age) as total\").show()\n",
    "df.selectExpr(\"*\", \"age+1000\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "2105571b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|name|\n",
      "+----+\n",
      "| tom|\n",
      "| jim|\n",
      "|jack|\n",
      "+----+\n",
      "\n",
      "+----+---+\n",
      "|name|age|\n",
      "+----+---+\n",
      "| jim| 20|\n",
      "|jack| 22|\n",
      "| tom| 24|\n",
      "+----+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select('name').distinct().show()\n",
    "df.select('name','age').sort('age').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d5ee9028",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+\n",
      "|name|age|\n",
      "+----+---+\n",
      "| tom| 24|\n",
      "|jack| 22|\n",
      "| jim| 20|\n",
      "+----+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.orderBy(df['age'].desc()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "84595d1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+\n",
      "|name|total|\n",
      "+----+-----+\n",
      "| tom| 24.0|\n",
      "| jim| 20.0|\n",
      "|jack| 22.0|\n",
      "+----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy(\"name\").agg( F.avg(\"age\").alias(\"total\")  ).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "b305b281",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+----+\n",
      "|name| age|\n",
      "+----+----+\n",
      "| tom|true|\n",
      "| jim|true|\n",
      "|jack|true|\n",
      "+----+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"*\").withColumn(\"age\", F.expr(\"age>1\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "26019d29",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------+\n",
      "|name|new_age|\n",
      "+----+-------+\n",
      "| tom|     24|\n",
      "| jim|     20|\n",
      "|jack|     22|\n",
      "+----+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.withColumnRenamed('age', 'new_age').show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7d47eea",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.select(df[''])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
